package biz.bbtec.sbm.user.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.user.bean.UserBean;

public class UserService {

	dbConnection db = new dbConnection();

	public UserBean Login(UserBean user) {
		UserBean userbean = new UserBean();
		String sql = "select * from user where user = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, user.getUser());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			userbean.setUser(rs.getString("user"));
			userbean.setPass(rs.getString("pass"));
			userbean.setName(rs.getString("name"));
			userbean.setEmail(rs.getString("email"));
			userbean.setOrgid(Integer.parseInt(rs.getString("orgid")));
			userbean.setPhone(rs.getString("phone"));
			userbean.setAlerttime(rs.getInt("alerttime"));
			userbean.setUserid(Integer.parseInt(rs.getString("userid")));
			userbean.setAlertstate(rs.getInt("alertstate"));
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return userbean;
	}

	public boolean exit(UserBean user) {
		boolean flag = false;
		String sql = "select orgid from orginfo";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				int orgid = Integer.parseInt(rs.getString(1));
				if (orgid == user.getOrgid()) {
					return true;
				}
			}
			past.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean check(UserBean user) {
		boolean flag = false;
		String sql = "select user from user";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				String use = rs.getString("user");
				if (use.equalsIgnoreCase(user.getUser())) {
					return true;
				}
			}
			past.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	/**
	 * @date 2014-02-22
	 * @author chenxu
	 * default alertstate is on,alerttime is 8 o'clock
	 */
	public boolean register(UserBean user) {
		boolean flag = false;
		user.setAlertstate(1);
		user.setAlerttime(8);
		String sql = "insert into user(orgid,user,pass,name,email,phone,alerttime,alertstate) values("
				+ "'"
				+ user.getOrgid()
				+ "'"
				+ ","
				+ "'"
				+ user.getUser()
				+ "'"
				+ ","
				+ "'"
				+ user.getPass()
				+ "'"
				+ ","
				+ "'"
				+ user.getName()
				+ "'"
				+ ","
				+ "'"
				+ user.getEmail()
				+ "'"
				+ ","
				+ "'"
				+ user.getPhone() 
				+ "'" 
				+ ","
				+ "'"
				+user.getAlerttime()
				+ "'" 
				+ ","
				+ "'"
				+user.getAlertstate()
				+ "'" 
				+ ")";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public UserBean show(UserBean userbean) {
		UserBean user = new UserBean();
		String sql = "select * from user where userid = " + "'"
				+ userbean.getUserid() + "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql);
			rs = past.executeQuery();
			if (!(rs.next())) {
				return null;
			}
			user.setUserid(userbean.getUserid());
			user.setUser(rs.getString("user"));
			user.setName(rs.getString("name"));
			user.setPhone(rs.getString("phone"));
			user.setPass(rs.getString("pass"));
			user.setRepass(user.getPass());
			user.setEmail(rs.getString("email"));
			user.setOrgid(rs.getInt("orgid"));
			user.setAlertstate(rs.getInt("alertstate"));
			int alerttime=rs.getInt("alerttime");
			if(alerttime==0)
				user.setAlerttime(8);
			else 
				user.setAlerttime(alerttime);
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			String sql1 = "select * from orginfo where orgid = " + "'"
					+ user.getOrgid() + "'";
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery();
			if (rs.next()) {
				user.setOrgname(rs.getString("name"));
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}

		return user;
	}

	public boolean Modify(UserBean userbean) {
		boolean flag = false;
		String sql = "update user set name=?, phone=?, pass=?, email=?,alerttime=?,alertstate=? where userid = "
				+ "'" + userbean.getUserid() + "'" ;
		PreparedStatement past = null;
		Connection conn = db.getConnection();
		try{
			past = conn.prepareStatement(sql);
			past.setString(1, userbean.getName());
			past.setString(2, userbean.getPhone());
			past.setString(3, userbean.getPass());
			past.setString(4, userbean.getEmail());
			past.setInt(6, userbean.getAlertstate());
			int alerttime=userbean.getAlerttime();
			if(alerttime==0){
				past.setInt(5,8);
			}
			else{
			past.setInt(5,alerttime);
			}
			past.executeUpdate();
			flag = true;
			past.close();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			db.closeConnection(conn);
		}
		return flag;
	}
}
